USE [influenza]
GO

/****** Object:  StoredProcedure [dbo].[SP_RptLabEdad]    Script Date: 02/27/2010 18:22:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[SP_RptLabEdad]
@param varchar(100)
as
Begin
declare @qry VARCHAR(8000),
@qrySelec varchar(100),
@edadInf varchar(4),
@edadMax varchar(4),
@id_laboratorio varchar(4)

set @edadInf=dbo.fnSplit(@param,'|',1)
set @edadMax=dbo.fnSplit(@param,'|',2)
set @id_laboratorio=dbo.fnSplit(@param,'|',3)

set @qry='
select [INF AH1N1 PAND],[A H1],[A H3],[B],[NEGATIVO],[NO ADECUADO],[ADENOVIRUS],[PARAINFLUENZA 1],
[PARAINFLUENZA 2],[PARAINFLUENZA 3],[VSR],[NO SUBTIPIFICADO],[H5],[INF A],[H1],[H3],[VSR A],
[VSR B],[CORONA 229E],[CORONA OC43],[CORONA SARS],[CORONA NL63],[CORONA HKU1],[NO AMPLIFICO],
[ENTEROV//RHINOVIRUS],[METAPNEUMOVIRUS],[SIN AISLAMIENTO],[A H5],[PARAINFLUENZA 4]
FROM(
	select * 
			from (Select c.resultados, b.nResultado
					from dat_generales a
					inner join dat_laboratorio b on(a.id_principal=b.id_generales)
					inner join cat_lab_resultados c on(b.nResultado=c.id_resultados)
					where dbo.fEdad(a.nDias,a.nMeses,a.nAnio) between '+@edadInf+' and '+@edadMax
					if @id_laboratorio<>0
					  begin
					     set @qry=@qry+' and b.id_laboratorio='+@id_laboratorio
					  end
SET @qry=@qry+' ) as Resultados 
	
)up PIVOT (count(nResultado) for resultados in ([INF AH1N1 PAND],[A H1],[A H3],[B],[NEGATIVO],[NO ADECUADO],[ADENOVIRUS],[PARAINFLUENZA 1],
[PARAINFLUENZA 2],[PARAINFLUENZA 3],[VSR],[NO SUBTIPIFICADO],[H5],[INF A],[H1],[H3],[VSR A],
[VSR B],[CORONA 229E],[CORONA OC43],[CORONA SARS],[CORONA NL63],[CORONA HKU1],[NO AMPLIFICO],
[ENTEROV//RHINOVIRUS],[METAPNEUMOVIRUS],[SIN AISLAMIENTO],[A H5],[PARAINFLUENZA 4])) as rptEdad'

exec(@qry)

End
GO

